﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using BCDataCenter.DataAccessLayer;
using System.Drawing;
using DevExpress.Web.ASPxPivotGrid;
using Excel = Microsoft.Office.Interop.Excel;

public partial class WebUserControl_ControlAllSaleForce : System.Web.UI.UserControl
{
   
   
    string error="";
    ClassExecute.ClassExecute dt;
    string str = BCDataCenter.Configuration.CurrentConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            getListTimeReport();
        }
        else
        {
            //try
            //{
            //    if (ViewState["db"] != null)
            //    {
            //        Title();
            //        DataTable db = (DataTable)ViewState["db"];
            //        ASPxPivotGrid1.DataSource = db;
            //        ASPxPivotGrid1.DataBind();
            //    }
            //}
            //catch
            //{
            //    return;
            //}
        }
    }
    private void getListTimeReport()
    {
        dt = new ClassExecute.ClassExecute();
        DataTable dbMonth = dt.Get_Data_DataTable(str, ref error, "k_getMonthList", CommandType.StoredProcedure);

            DropDownListDateReport.DataSource = dbMonth;
            DropDownListDateReport.DataTextField = "Names";
            DropDownListDateReport.DataValueField = "Values";
            DropDownListDateReport.DataBind();
       
        
    }
    public void Title()
    {
        if (!ASPxPivotGrid1.Fields.Contains(ASPxPivotGrid1.Fields["Baocao"]))
        {
            ASPxPivotGrid1.Fields.Add("Baocao", DevExpress.XtraPivotGrid.PivotArea.FilterArea);
            ASPxPivotGrid1.Fields["Baocao"].HeaderStyle.BackColor = Color.Yellow;
            ASPxPivotGrid1.Fields["Baocao"].Caption = "Báo cáo ALL SALES FORCE";
        }
        if (!ASPxPivotGrid1.Fields.Contains(ASPxPivotGrid1.Fields["Time"]))
        {
            ASPxPivotGrid1.Fields.Add("Time", DevExpress.XtraPivotGrid.PivotArea.FilterArea);
            ASPxPivotGrid1.Fields["Time"].HeaderStyle.BackColor = Color.Yellow;
            ASPxPivotGrid1.Fields["Time"].Caption = "Ngày giờ lấy báo cáo : " + DateTime.Now.ToShortDateString() + "  " + DateTime.Now.ToShortTimeString();
        }
    }
    private void get_data()
    {
        SqlConnection conn = null;
        int i = 0;
        try
        {
            // create and open a connection object
            conn = new SqlConnection(str);
            conn.Open();

            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd = new SqlCommand("[ls_GenerateAllSalesForce_CS2011]", conn);

            // 2. set the command object so it knows
            //    to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            //    will be passed to the stored procedure
            cmd.Parameters.Add(new SqlParameter("@MmYyyy", DropDownListDateReport.SelectedValue.ToString()));
            cmd.Parameters.Add(new SqlParameter("@UserName", Session["username"].ToString()));
            cmd.CommandTimeout = 500;
            cmd.ExecuteNonQuery();
            i = 1;
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
        finally
        {
            conn.Close();
        }
        if (i == 1)
        {
            try
            {
                //ViewState["db"] = null;
                dt = new ClassExecute.ClassExecute();
                DataTable  db = dt.Get_Data_DataTable(str, ref error, "[ls_getAllSalesForce_CS2011]", CommandType.StoredProcedure,
                    new SqlParameter("@UserName", Session["username"].ToString()),
                    new SqlParameter("@YyyyMm", DropDownListDateReport.SelectedValue.ToString().Substring(2) + DropDownListDateReport.SelectedValue.ToString().Substring(0,2)),
                    new SqlParameter("@mBARange", Int32.Parse(rbl1.SelectedValue.ToString())));
                //ViewState["db"] = db;
                ASPxPivotGrid1.Fields.Clear();
                Title();
                ASPxPivotGrid1.CustomizationFieldsLeft = 500;
                ASPxPivotGrid1.CustomizationFieldsTop = 500;
                ASPxPivotGrid1.OptionsDataField.AreaIndex = 0;

                foreach (DataColumn col in db.Columns)
                {
                    bool check = IsNumeric(col.ToString().Substring(0, 1));

                    if (check)
                    {
                        ASPxPivotGrid1.Fields.Add(col.ColumnName.ToString(), DevExpress.XtraPivotGrid.PivotArea.DataArea);
                    }
                    else
                    {
                        ASPxPivotGrid1.Fields.Add(col.ColumnName.ToString(), DevExpress.XtraPivotGrid.PivotArea.RowArea);
                    }

                }

                for (int j = 15; j < ASPxPivotGrid1.Fields.Count; j++)
                {
                    ASPxPivotGrid1.Fields[j].ValueFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    ASPxPivotGrid1.Fields[j].ValueFormat.FormatString = "{0:###,###}";

                    ASPxPivotGrid1.Fields[j].CellFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
                    ASPxPivotGrid1.Fields[j].CellFormat.FormatString = "{0:###,###}";
                }
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
                
            }
            catch (Exception ex)
            {
                uscMsgBox1.AddMessage(ex.Message, uscMsgBox.enmMessageType.Error);
            }
        }
        else
        {
            uscMsgBox1.AddMessage(i.ToString(), uscMsgBox.enmMessageType.Error);
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            get_data();
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //generatePivotTable(Server.MapPath("filetemp.xls"));
        Step1GenerateBindDataToSheet(Server.MapPath(Session["username"].ToString() + "_BaoCaoAllSalesForce.xls"));
        saveFile(this.Response, Server.MapPath(Session["username"].ToString() + "_BaoCaoAllSalesForce.xls"));
        //Step2BindSheet1ToPivot(Server.MapPath("filetemp.xls"),Server.MapPath("BaoCaoAllSalesForce.xls"));
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch
        {

        }
        finally
        {
            GC.Collect();
        }
    }
    public bool IsNumeric(string anyString)
    {
        if (anyString == null)
        {
            anyString = "";
        }
        if (anyString.Length > 0)
        {
            double dummyOut = new double();
            System.Globalization.CultureInfo cultureInfo =
                new System.Globalization.CultureInfo("en-US", true);

            return Double.TryParse(anyString, System.Globalization.NumberStyles.Any,
                cultureInfo.NumberFormat, out dummyOut);
        }
        else
        {
            return false;
        }
    }
    public void Step1GenerateBindDataToSheet(string path)
    {
        if (File.Exists(path))
        {
            File.Delete(path);
        }
        Excel.Application m_objExcel;
        Excel.Workbooks m_objBooks;
        Excel._Workbook m_objBook;

        Excel.Sheets m_objSheets;
        Excel._Worksheet m_objSheet;

        Excel.Range m_objRange;

        object m_objOpt = System.Reflection.Missing.Value;

        Excel.QueryTables m_objQryTables;
        Excel._QueryTable m_objQryTable;

        // Start a new workbook in Excel.
        m_objExcel = new Excel.Application();
        m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
        m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

        // Create a QueryTable that starts at cell A1.
        m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
        m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

        //Thêm title cho báo cáo
        Excel.Style style = m_objBook.Styles.Add("Style1", m_objOpt);
        style.Font.Bold = true;
     
        m_objSheet.Activate();
        Excel.Range range = ((Excel.Range)m_objSheet.Cells[1, 1]);
        range.Style = style;
        range.Value2 = "BÁO CÁO ALL SALES FORCE";
        Excel.Range range1 = ((Excel.Range)m_objSheet.Cells[2, 1]);
        range1.Style = style;
        range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
        Excel.Range range2 = ((Excel.Range)m_objSheet.Cells[3, 1]);
        range2.Style = style;
        range2.Value2 = "Tháng: " + DropDownListDateReport.SelectedItem.ToString();

        //
        m_objRange = m_objSheet.get_Range("A4", m_objOpt);
        m_objQryTables = m_objSheet.QueryTables;
        //@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;"
        m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(@"OLEDB;Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;" + str, m_objRange, "select * from DML_" + Session["username"].ToString() + "_AllSalesForce");
        m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
        m_objQryTable.Refresh(false);

        // Save the workbook and quit Excel.
        m_objBook.SaveAs(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        m_objBook.Close(false, m_objOpt, m_objOpt);
        m_objExcel.Quit();

        //clear
        releaseObject(m_objSheet);
        releaseObject(m_objBook);
        releaseObject(m_objExcel);
    }
    public void Step2BindSheet1ToPivot(string pathSource, string pathDestination)
    {

        if (File.Exists(pathDestination))
        {
            File.Delete(pathDestination);
        }

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Sheets m_objSheets;
        Excel._Worksheet xlWorkSheet;
        Excel.Range m_objRange;

        object m_objOpt = System.Reflection.Missing.Value;

        string str;

        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open(pathSource, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        //create a worksheet   
        m_objSheets = (Excel.Sheets)xlWorkBook.Worksheets;
        xlWorkSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

        m_objRange = xlWorkSheet.UsedRange;

        ((Excel.Worksheet)xlApp.ActiveWorkbook.Sheets[1]).Visible = Excel.XlSheetVisibility.xlSheetHidden;

        //m_objSheets = (Excel.Sheets)xlWorkBook.Worksheets;
        xlWorkSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
        xlWorkSheet.Name = "BaoCaoAllSalesForce";

        //Thêm title cho báo cáo
        Excel.Style style = xlWorkBook.Styles.Add("Style1", m_objOpt);
        style.Font.Bold = true;
        style.Font.Size = 32;
        xlWorkSheet.Activate();
        Excel.Range range = ((Excel.Range)xlWorkSheet.Cells[1, 1]);
        range.Style = style;
        range.Value2 = "BÁO CÁO ALL SALES FORCE";
        Excel.Range range1 = ((Excel.Range)xlWorkSheet.Cells[2, 1]);
        range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
        Excel.Range range2 = ((Excel.Range)xlWorkSheet.Cells[3, 1]);
        range2.Value2 = "Tháng: " + DropDownListDateReport.SelectedItem.ToString();

        //tạo pivot
        Excel.PivotCaches pivotCaches = xlWorkBook.PivotCaches();
        pivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, m_objRange).CreatePivotTable(xlWorkSheet.Cells[20, 1], "Piv1", m_objOpt, m_objOpt);


        Excel.PivotTable pivotTable = xlWorkSheet.PivotTables("Piv1") as Excel.PivotTable;

        int nCols = m_objRange.Columns.Count;

        for (int j = 1; j <= m_objRange.Columns.Count; j++)
        {
            str = (string)(m_objRange.Cells[1, j] as Excel.Range).Value2;
            Excel.PivotField pageField17 = (Excel.PivotField)pivotTable.PivotFields(str);
            pageField17.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField17.Caption = str;
        }


        // Save the workbook and quit Excel.
        xlWorkBook.SaveAs(pathDestination, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        xlWorkBook.Close(false, m_objOpt, m_objOpt);
        xlApp.Quit();

        //clear
        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        saveFile(this.Response, pathDestination);
    }
    public void generatePivotTable(string path)
    {
        try
        {
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            Excel.Application m_objExcel;
            Excel.Workbooks m_objBooks;
            Excel._Workbook m_objBook;

            Excel.Sheets m_objSheets;
            Excel._Worksheet m_objSheet;

            Excel.Range m_objRange;

            object m_objOpt = System.Reflection.Missing.Value;

            Excel.QueryTables m_objQryTables;
            Excel._QueryTable m_objQryTable;

            // Start a new workbook in Excel.
            m_objExcel = new Excel.Application();
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));


            //create a worksheet   
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
            m_objSheet.Name = "Basic";
            // Create a QueryTable that starts at cell A1.
            m_objRange = m_objSheet.get_Range("A1", m_objOpt);
            m_objQryTables = m_objSheet.QueryTables;
            m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + str, m_objRange, "select * from DML_" + Session["username"].ToString() + "AllSalesForce");
            m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
            m_objQryTable.Refresh(false);

            m_objRange = m_objSheet.UsedRange;



            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
            m_objSheet.Name = "BaoCaoAllSalesForce";

            //Thêm title cho báo cáo
            Excel.Style style = m_objBook.Styles.Add("Style1", m_objOpt);
            style.Font.Bold = true;
            style.Font.Size = 32;
            m_objSheet.Activate();
            Excel.Range range = ((Excel.Range)m_objSheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO ALL SALES FORCE";
            Excel.Range range1 = ((Excel.Range)m_objSheet.Cells[2, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
            Excel.Range range2 = ((Excel.Range)m_objSheet.Cells[3, 1]);
            range2.Value2 = "Tháng: " + DropDownListDateReport.SelectedItem.ToString();

            //tạo pivot
            Excel.PivotCaches pivotCaches = m_objBook.PivotCaches();
            pivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, m_objRange).CreatePivotTable(m_objSheet.Cells[20, 1], "Piv1", m_objOpt, m_objOpt);


            Excel.PivotTable pivotTable = m_objSheet.PivotTables("Piv1") as Excel.PivotTable;

            int nCols = m_objRange.Columns.Count;

            for (int cCnt = 1; cCnt <= m_objRange.Columns.Count; cCnt++)
            {
                string str1 = (string)(m_objRange.Cells[1, cCnt] as Excel.Range).Value2;
                Excel.PivotField pageField17 = (Excel.PivotField)pivotTable.PivotFields(str1);
                bool check = IsNumeric(str1.Substring(0, 1));

                if (check)
                {
                    pageField17.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
                }
                else
                {
                    pageField17.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

                }
                pageField17.Caption = str;
            }


            // Save the workbook and quit Excel.
            m_objBook.SaveAs(path, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            m_objBook.Close(false, m_objOpt, m_objOpt);
            m_objExcel.Quit();

            saveFile(this.Response, path);
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
}
